package luke4fun.wheresmymoney.service.async;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.AsyncTask;

import java.util.ArrayList;
import java.util.List;

import luke4fun.wheresmymoney.adapter.list.BasicAdapter;
import luke4fun.wheresmymoney.bean.BudgetBean;
import luke4fun.wheresmymoney.bean.BudgetDetailBean;
import luke4fun.wheresmymoney.db.AccDBHelper;
import luke4fun.wheresmymoney.db.DBConstants;

public class EditBudgetDetailAsyncTask extends AsyncTask<Long, Long, List<BudgetDetailBean>> {

	private Context context;
	private BasicAdapter<BudgetDetailBean> adapter;
	
	public EditBudgetDetailAsyncTask(Context context, BasicAdapter<BudgetDetailBean> adapter) {
		this.context = context;
		this.adapter = adapter;
	}
	
	@Override
	protected List<BudgetDetailBean> doInBackground(Long... params) {
		AccDBHelper dbHelper = new AccDBHelper(context);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		
		/**
		 * select * from category
			left join
			(
				select * from (select * from budget where _id = null) tb1
				left join
				(select pid, category, budget as p_budget from budget_detail where pid = null) tb2
				on tb1._id = tb2.pid
			) t on category._id = t.category
		 */
		StringBuilder sb = new StringBuilder("SELECT * FROM ");
		sb.append(DBConstants.TABLE_CATEGORY)
		  .append(" LEFT JOIN (")
		  .append(" SELECT * FROM (")
		  .append(" SELECT * FROM ")
		  .append(DBConstants.TABLE_BUDGET)
		  .append(" WHERE _id=?) tb1")
		  .append(" LEFT JOIN (")
		  .append("SELECT * FROM ")
		  .append(DBConstants.TABLE_BUDGET_DETAIL)
		  .append(" WHERE pid=?) tb2")
		  .append(" ON tb1._id=tb2.pid")
		  .append(") t")
		  .append(" ON category._id=t.category")
		  .append(" ORDER BY ")
		  .append(DBConstants.CATEGORY_FREQUENCE)
		  .append(" DESC");
		
		String[] args = null;
		if (params != null && params.length > 0) {
			args = new String[] {String.valueOf(params[0])};
		}
		
		Cursor cursor = db.rawQuery(sb.toString(), args);
//		Log.d("ZJ", "columns: " + cursor.getColumnCount());
//		for (int i=0; i<cursor.getColumnCount(); i++) {
//			Log.d("ZJ", "column("+i+"): " + cursor.getColumnName(i));
//		}
		
		List<BudgetDetailBean> beanList = new ArrayList<BudgetDetailBean>(0);
		while (cursor.moveToNext()) {
			Long categoryId 	  = cursor.getLong(0);
			String categoryName   = cursor.getString(cursor.getColumnIndex(DBConstants.CATEGORY_NAME));
			Integer categoryColor = cursor.getInt(cursor.getColumnIndex(DBConstants.CATEGORY_COLOR));
			
			BudgetBean bean 	 = new BudgetBean();
			BudgetDetailBean dtl = new BudgetDetailBean();
			dtl.setCategoryId(categoryId);
			dtl.setCategory(categoryName);
			dtl.setCategoryColor(categoryColor);
			dtl.setBudget(0);
			
			if (args != null) {
				Long budgetId   = null;
				Long budgetFrom = null;
				Long budgetTo   = null;
				Integer budget  = null;
				Long dtlId		= null;
				Long dtlPid		= null;
				Integer dtlBudget= null;
				
				int index = cursor.getColumnIndex("t."+DBConstants.BUDGET_ID);
				if (!cursor.isNull(index)) {
					budgetId = cursor.getLong(index);
				}
				index = cursor.getColumnIndex("t."+DBConstants.BUDGET_FROM);
				if (!cursor.isNull(index)) {
					budgetFrom = cursor.getLong(index);
				}
				index = cursor.getColumnIndex("t."+DBConstants.BUDGET_TO);
				if (!cursor.isNull(index)) {
					budgetTo = cursor.getLong(index);
				}
				index = cursor.getColumnIndex("t."+DBConstants.BUDGET_BUDGET);
				if (!cursor.isNull(index)) {
					budget = cursor.getInt(index);
				}
				
				bean.setId(budgetId);
				bean.setFrom(budgetFrom);
				bean.setTo(budgetTo);
				bean.setBudget(budget);
				
				index = cursor.getColumnIndex("t."+DBConstants.BUDGET_DETAIL_ID+":1");
				if (!cursor.isNull(index)) {
					dtlId = cursor.getLong(index);
				}
				index = cursor.getColumnIndex("t."+DBConstants.BUDGET_DETAIL_PID);
				if (!cursor.isNull(index)) {
					dtlPid = cursor.getLong(index);
				}
				index = cursor.getColumnIndex("t."+DBConstants.BUDGET_DETAIL_BUDGET+":1");
				if (!cursor.isNull(index)) {
					dtlBudget = cursor.getInt(index);
				}
				
				dtl.setId(dtlId);
				dtl.setPid(dtlPid);
				dtl.setBudget(dtlBudget);
				dtl.setMax(budget);
			}

			dtl.setParent(bean);
			beanList.add(dtl);
		}
		
		db.close();
		dbHelper.close();
		return beanList;
	}

	@Override
	protected void onPostExecute(List<BudgetDetailBean> list) {
		adapter.updateList(list);
	}
}
